/**
 * 数据库操作工具类
 * create by jack at 2023年5月24日
 */

// 加载数据库配置
import type { PoolOptions } from 'mysql2'
import mysql from 'mysql2/promise'
import logger from '../utils/logger'

let pool
interface AllObj {
  [index: string]: any
}

function convertKeys(obj: any) {
  if (Array.isArray(obj)) {
    return obj.map(item => convertKeys(item))
  }
  else if (obj !== null && typeof obj === 'object' && !(obj instanceof Date)) {
    const converted: { [key: string]: any } = {}
    for (const key in obj) {
      if (Object.prototype.hasOwnProperty.call(obj, key)) {
        const convertedKey = key.replace(/_(\w)/g, (_, letter) => letter.toUpperCase())
        converted[convertedKey] = convertKeys(obj[key])
      }
    }
    return converted
  }
  return obj
}

/**
 * 数据库查询工具，返回promise，默认将下划线转驼峰
 * @param sql 查询语句
 * @param params 查询语句参数，用数据传递
 * @param underlineToHump 是否转驼峰，默认为true
 */
export default async <T extends AllObj[] | AllObj>(sql: string, params: Array<any>, underlineToHump = true): Promise<T> => {
  if (!pool) {
    // 每次使用的时候需要创建链接，数据操作完成之后要关闭连接
    const { DB_HOST, DB_DATABASE, DB_PASSWORD, DB_USER } = process.env
    const config: PoolOptions = {
      host: DB_HOST,
      user: DB_USER,
      password: DB_PASSWORD,
      database: DB_DATABASE,
      waitForConnections: true,
      connectionLimit: 10, // 调整连接池的大小根据你的需求
    }
    pool = mysql.createPool(config)
  }
  const connection = await pool.getConnection()
  // 查询数据库
  const [results] = await connection.execute(sql, params)
  logger.info('SQL:', connection.format(sql, params), 'params:', params, 'response:', results)
  connection.release()
  // 处理返回数据并返回
  return underlineToHump ? convertKeys(results) : results as T
}
